package com.h819.core.db.examples;

import com.h819.core.db.DataSourceUtils;
import org.apache.commons.dbcp.BasicDataSource;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.*;
import org.apache.commons.lang.StringUtils;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.*;

import static java.lang.System.out;

//依赖包：
//commons-dbcp-1.2.2.jar：提供数据库连接池
//commons-dbutils-1.3.jar：DbUtils包
//commons-lang-2.4.jar：提供常用的工具方法
//commons-pool-1.5.3.jar：提供DBCP对象池化机制
//mysql-connector-java-5.1.10-bin.jar：MySQL JDBC4.0驱动

//参考：http://blog.csdn.net/inkfish/archive/2010/01/01/5118790.aspx
/**
 * @Title: QueryRunnerExample.java
 * @Description: TODO(演示了如何使用QueryRunner 、ResultSetHandler)
 * @author H819
 * @date 2010-3-16
 * @version V1.0
 */
public class DbUtilsQueryRunnerExample {

	public static void main(String[] args) throws SQLException {

		DbUtilsQueryRunnerExample example = new DbUtilsQueryRunnerExample();
		// example.batch();
		// example.fillStatement();
		example.query();
		// example.update();
		// example.closeDataSource();
	}

	private DataSource dataSource = null;
	private QueryRunner runner = null;
	private Connection conn = null;

	// 关于 QueryRunner 方法
	// === 如果已知数据源
	// 构造方法用 new QueryRunner(dataSource)
	// 之后调用它的不包含 connection 的查询方法
	// 其 connection 会自动在数据源中获取
	//	 
	// === 不知道数据源，知道 connection
	// 构造方法用 new QueryRunner()
	// 调用它的包含 connction 的查询方法
	//	

	public DbUtilsQueryRunnerExample() {
		initDataSource();
		runner = new QueryRunner(dataSource);
	}

	private void batch() {
		String sql = "INSERT INTO user (USERNAME, PASSWORDHASH) values (?, ?)";
		try {
			out.println("\n"
					+ StringUtils.center("Test QueryRunner batch", 80, '*'));
			int[] result = runner.batch(sql, new Object[][] {
					{ "user1", "pwd1" }, { "user2", "pwd2" },
					{ "user3", "pwd3" }, { "user4", "pwd4" } });
			out.printf("运行结果：%s\n", Arrays.toString(result));
		} catch (SQLException ex) {
			DbUtils.printStackTrace(ex);
		}
	}

	private void update() {
		String sql = "DELETE FROM user WHERE PASSWORDHASH LIKE ?";
		try {
			out.println("\n"
					+ StringUtils.center("Test QueryRunner update", 80, '*'));
			out.println(runner.update(sql, "pwd%"));
		} catch (SQLException ex) {
			DbUtils.printStackTrace(ex);
		}
	}

	private void fillStatement() throws SQLException {
		String sql = "INSERT INTO user (USERNAME, PASSWORDHASH) VALUES (?, ?)";
		Connection conn = null;
		try {
			out.println("\n"
					+ StringUtils.center("Test QueryRunner fillStatement", 80,
							'*'));
			conn = dataSource.getConnection();
			PreparedStatement psmt = conn.prepareStatement(sql);
			runner.fillStatementWithBean(psmt, new User("testUser5", "pwd5"),
					"userName", "passwordHash");
			out.println(psmt.executeUpdate());
		} catch (SQLException ex) {
			DbUtils.printStackTrace(ex);
		} finally {
			DbUtils.commitAndCloseQuietly(conn);
		}
	}

	private void query() throws SQLException {

		/*
		 * 如果查询不到结果，那么返回的集合 size = 0
		 */

		out.println("\n"
				+ StringUtils.center("Test QueryRunner query", 80, '*'));

		String sql = "SELECT * FROM user WHERE USERNAME LIKE ?";

		// ====== 把ResultSet第一行包装成Object[]，返回值按照 sql 查询字段的顺序返回。对象数组，数组元素的顺序和查询语句列的顺序相同.
		//如果无记录返回，则 r1==null
		out.println("1.Test QueryRunner query, ArrayHandler");
		Object[] r1 = runner.query(sql, new ArrayHandler(), "user%");
		out.println("  " + Arrays.deepToString(r1));

		// ====== 把ResultSet包装成List<Object[]>
		out.println("2.Test QueryRunner query, ArrayListHandler");
		List<Object[]> r2 = runner.query(sql, new ArrayListHandler(), "user%");
		out.println("  " + Arrays.deepToString(r2.toArray()));

		// ====== 把ResultSet第一行包装成一个JavaBean
		out.println("3.Test QueryRunner query, BeanHandler");
		User r3 = runner.query(sql, new BeanHandler<User>(User.class), "user%");
		out.println("  " + r3.toString());

		// ====== 把ResultSet第一行包装成一个List<JavaBean>；
		out.println("4.Test QueryRunner query, BeanListHandler");
		List<User> r4 = runner.query(sql,
				new BeanListHandler<User>(User.class), "user%");
		out.println("  " + Arrays.deepToString(r4.toArray()));

		// ====== 抽取ResultSet指定的列(参数为列明)，以List<Object>对象的形式返回，默认第一列
		out.println("5.Test query, ColumnListHandler");
		List<Object> r5 = runner.query(sql, new ColumnListHandler("clumnname"),
				"user%");
		out.println("  " + Arrays.deepToString(r5.toArray()));

		// ======
		// 包装ResultSet，以Map<Object,Map<String,Object>>对象形式返回，第一个Object是指定的列值，第二个Map中String是列名且对大小写不敏感
		out.println("6.Test QueryRunner query, KeyedHandler");
		Map<Object, Map<String, Object>> r6 = runner.query(sql,
				new KeyedHandler(2), "user%");
		out.println("  " + r6.toString());

		/*
		 * MapHandler,MapListHandler 读取视图问题
		 */

		// MapHandler,MapListHandler 中，map 的 key 是真实的列名，不是虚拟的的视图的列名。
		// 这时候如果视图关联的不同的表中，如果有相同的列名，那么就会产生混淆
		// 所以 MapHandler,MapListHandler 不能用于视图查询。
		// 改用 ArrayListHandler，之后重新包装为 Map
		// 例如：
		List<Map<String, Object>> linkResult = new ArrayList<Map<String, Object>>();
		List<Object[]> listTemp;

		listTemp = runner.query(conn, "select name,password from view",
				new ArrayListHandler());
		// 由于查询语句是试图，所以重新组装数据
		// 序号 和查询语句顺序一致
		for (Object[] o : listTemp) {
			Map<String, Object> m = new HashMap<String, Object>();
			m.put("name", o[0]);
			m.put("password", o[1]);
			linkResult.add(m);

		}
		/*
		 */

		// ====== 结果为单行，把ResultSet第一行包装成Map<String, Object>，key对大小写不敏感
		out.println("7.Test QueryRunner query, MapHandler");
		Map<String, Object> r7 = runner.query(sql, new MapHandler(), "user%");
		for (Iterator it = r7.entrySet().iterator(); it.hasNext();) {
			Map.Entry entry = (Map.Entry) it.next();
			System.out.println(entry.getKey() + " = " + entry.getValue());
		}

		// ====== 结果为多行，把ResultSet包装成List<Map<String,Object>>，Map的key对大小写不敏感
		out.println("8.Test QueryRunner query, MapListHandler");
		List<Map<String, Object>> r8 = runner.query(sql, new MapListHandler(),
				"user%");
		for (Map m : r8)
			for (Iterator it = m.entrySet().iterator(); it.hasNext();) {
				Map.Entry entry = (Map.Entry) it.next();
				System.out.println(entry.getKey() + " = " + entry.getValue());
			}

		// ======抽取ResultSet第一行指定列，以Object对象形式返回
		out.println("9.Test QueryRunner query, ScalarHandler");
		Object r9 = runner.query(sql, new ScalarHandler("passwordhash"),
				"user%");
		out.println("  " + r9.toString());
	}

	private DataSource initDataSource() {
		if (dataSource == null) {
			BasicDataSource basicDs = new BasicDataSource();
			basicDs.setDriverClassName("com.mysql.jdbc.Driver");
			basicDs.setUrl("jdbc:mysql://127.0.0.1:3306/test");
			basicDs.setUsername("root");
			basicDs.setPassword("123456");
			this.dataSource = basicDs;
		}
		DataSourceUtils.printDataSourceStats(dataSource);
		return dataSource;
	}

}